SQL复杂查询(子查询)

USE STUDY
SELECT * from EMP
SELECT * FROM SALGRADE 

--1.查询雇员姓名,所在部门编号和名称

SELECT ename,EMP.deptno,DEPT.deptno FROM EMP INNER JOIN DEPT ON DEPT.deptno =EMP.deptno

--2.查询雇员姓名,工作,领导的姓名

SELECT e1.ename,e1.job,e2.ename FROM EMP e1 INNER JOIN EMP e2 on e1.mgr=e2.empno 

--3.查询雇员姓名,工作,领导姓名及部门名称

SELECT e1.ename,e1.job,e2.ename,DEPT.dname FROM DEPT , EMP e1 INNER JOIN EMP e2 ON e1.mgr=e2.empno WHERE DEPT.deptno =e1.deptno

--4.查询雇员姓名,工作,工资及工资等级

SELECT ename,job ,sal,SALGRADE.grade FROM EMP left JOIN SALGRADE ON sal BETWEEN losal AND hisal 

--5.查询雇员姓名,工作,工资及工资等级,要求工资等级显示为
--A B C D E

SELECT EMP.ename,EMP.job,EMP.sal,
CASE SALGRADE.grade
WHEN '1' THEN 'E' 
WHEN '2' THEN 'D' 
WHEN '3' THEN 'C' 
WHEN '4' THEN 'B'
WHEN '5' THEN 'A' 
END AS grade 
FROM EMP left join SALGRADE on EMP.sal BETWEEN losal AND hisal 

--6.查询雇员姓名,工作,领导的姓名 (左连接 =(+) :以左表为主)

SELECT e1.ename,e1.job,e2.ename FROM EMP e1 LEFT JOIN EMP e2 on e1.mgr=e2.empno

--7.查询雇员姓名,年薪,按年薪从高到低排序

SELECT ename,sal*12 years FROM EMP ORDER BY years DESC 

--8.查询每个部门中工资最高的雇员姓名,工作,工资,部门名称,最后按工资从高到低排序

--SELECT ename,job,sal,DEPT.dname FROM DEPT ,EMP where sal ORDER BY empno 
select a.* from emp a,(select deptno,max(sal) sal from emp group by deptno )b where a.deptno=b.deptno and a.sal=b.sal

--9.查询每个部门的雇员数量

SELECT deptno,count(*) from EMP GROUP BY deptno

--10.求出每个部门的平均工资

SELECT deptno ,avg (sal) FROM EMP GROUP BY deptno 

--11.按部门分组，并显示部门的名称，以及每个部门的员工数

SELECT count(*)FROM EMP GROUP BY deptno 

--12.要求显示平均工资大于2000的部门编号和平均工资

SELECT deptno,avg(sal)FROM EMP GROUP BY deptno HAVING avg(sal)>2000 

--13.显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员
--的月工资大于$1500，输出结果按月工资的合计升序排列

SELECT job,sum(sal) FROM EMP WHERE sal>1500 and job <>'Salesman' GROUP BY job ORDER BY sum(sal) 

--14.求出平均工资最高的部门

select top 1 avg(sal),deptno from emp group by deptno ORDER BY avg(sal) DESC 

--15.要求查询出比7654工资要高的全部雇员的信息

select * from EMP WHERE sal>(SELECT sal FROM EMP where empno=7654)

--要求查询工资比7654高，与7788从事相同工作的全部雇员信息

select * from EMP WHERE sal>(SELECT sal FROM EMP where empno=7654)AND
job =(SELECT job FROM EMP WHERE empno =7788)

--16.查询出工资最低的雇员姓名，工作，工资 

SELECT ename,job,sal FROM EMP WHERE sal =(SELECT min(sal)FROM EMP)